package softwareengineering_lab456;

import entity.Product;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class BasicConnectionSQL {

    public final static String DRIVER = "com.mysql.jdbc.Driver";
    public final static String URL = "jdbc:mysql://127.0.0.1:3306/shop?user=root&password=root";
    private Connection connection = null;
    private PreparedStatement prepared_statement = null;
    private ResultSet result_set = null;

    public Connection getConn() throws Exception {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL);
        return connection;
    }

    public int executeSQL(String preparedSql, String[] param) throws Exception {
        int num = 0;
        connection = getConn();
        prepared_statement = connection.prepareStatement(preparedSql);
        if (param != null) {
            for (int i = 0; i < param.length; i++) {
                prepared_statement.setString(i + 1, param[i]);
            }
        }
        num = prepared_statement.executeUpdate();
        prepared_statement.close();
        connection.close();
        return num;
    }

    public String getPWD_mySQL(String username) throws Exception {
        String FinalPass = null;
        connection = getConn();
        prepared_statement = connection.prepareStatement("select * from user where user_name = ?");
        prepared_statement.setString(1, username);
        result_set = prepared_statement.executeQuery();
        while (result_set.next()) {
            FinalPass = result_set.getString("SSN");
        }
        result_set.close();
        prepared_statement.close();
        connection.close();
        return FinalPass;
    }

    public int getQuantity(int id) throws Exception {
        int count = 0;
        connection = getConn();
        prepared_statement = connection.prepareStatement("select quantity from product where id = ?");
        prepared_statement.setString(1, String.valueOf(id));
        result_set = prepared_statement.executeQuery();
        while (result_set.next()) {
            count = result_set.getInt("quantity");
        }
        result_set.close();
        prepared_statement.close();
        connection.close();
        return count;
    }

    public ArrayList findAllProduct() throws Exception {
        ArrayList list = new ArrayList();
        connection = this.getConn();
        prepared_statement = connection.prepareStatement("select * from product ");
        result_set = prepared_statement.executeQuery();
        while (result_set.next()) {
            Product product = new Product();
            product.setName(result_set.getString("name"));
            product.setId(result_set.getInt("id"));
            product.setPrice(result_set.getInt("price"));
            product.setType(result_set.getString("type"));
            product.setQuantity(result_set.getInt("Quantity"));
            list.add(product);
        }
        result_set.close();
        prepared_statement.close();
        connection.close();
        return list;
    }
}